* Begin log file
capture log close sublog
log using "$projdir/log/3_laus_build.txt", name(sublog) text replace

*-------------------------------------------------------------------------------
* Description: Clean and merge the 1990-2022 LAUS data
* Author: Justin Wiltshire
*
* Updated: July 25, 2023
*-------------------------------------------------------------------------------

* Make directory
capture mkdir "$projdir/dta/build/src/laus"

* Loop over all years
forval i = 0/32 {
	
	* Create the year value as in the data names
	if inrange(`i', 0, 9) {
		local y "9`i'"
	}
	else if inrange(`i', 10, 19) {
		local j = `i' - 10
		local y "0`j'"
	}
	else if `i' > 19 {
		local j = `i' - 10
		local y "`j'"
	}

	local f = 1
		
	* Load the data
	#delimit ;
		qui import excel 
			using "$projdir/dta/build/src/laus/laucnty`y'.xlsx", 
			clear;
		#delimit cr	
		
	* Re-structure the data
	qui drop if inrange(_n, 1, 6) | mi(B)
	qui keep B C E G-I
	qui destring _all, ignore("N.A.") replace
	
	* Create cty_fips. Clean up the data
	qui gen cty_fips = 1000*B + C
	drop B C
	qui rename E year
	qui rename G lbf
	qui rename H emp
	qui rename I ump
	qui label var cty_fips "County FIPS"
	qui label var year "Year"
	qui label var lbf "Labour Force (level)"
	qui label var emp "Employed (level)"
	qui label var ump "Unemployed (level)"
	
	* Append the data
	if `i' > 0 {
		qui append using "$projdir/dta/build/cln/laus_cty.dta"
	}
	
	* Compress and save
	qui compress
	qui save "$projdir/dta/build/cln/laus_cty.dta", replace
}
tempfile core
qui save "`core'", replace

* Keep only contiguous US
drop if inlist(floor(cty_fips/1000), 2, 15, 72)

* Make county change adjustments and sum by remaining counties
qui do "$projdir/do/build/cw/cty_changes.do"
qui collapse (sum) lbf emp ump, by(cty_fips year)

* Recode 0 as missing
qui recode lbf emp ump (0=.)

* Generate unemployment rate
qui gen unr = ump/lbf
qui label var unr "Unemployment rate (%)"

* Order, sort, and save
qui order cty_fips year
qui sort cty_fips year
qui save "$projdir/dta/build/cln/laus_cty.dta", replace

* Save a readme file if the data were downloaded in this run
if `f' == 1 {
	qui clear all
	qui set obs 1
	qui gen readme = "Data downloaded on $S_DATE from: https://www.bls.gov/lau/#cntyaa"
	export delimited readme using "$projdir/dta/build/src/laus/readme.txt", replace novarnames
}

* Load the data
qui use "$projdir/dta/build/cln/laus_cty.dta", clear

* Create the state variable
qui gen state_fips = floor(cty_fips/1000)

* Aggregate to the state level
qui collapse (sum) emp ump lbf, by(state year)

* Create unr
qui gen unr = ump/lbf

* Compress and save
qui compress
qui save "$projdir/dta/build/cln/laus_state.dta", replace

*-------------------------------------------------------------------------------
* Repeat for county-level data, but do not combine NYC counties
*-------------------------------------------------------------------------------

* Re-load downloaded data
qui use "`core'", clear

* Keep only contiguous US
drop if inlist(floor(cty_fips/1000), 2, 15, 72)

* Make non-NYC county change adjustments and sum by remaining counties
qui do "$projdir/do/build/cw/cty_changes_NYCnocombine.do"
qui collapse (sum) lbf emp ump, by(cty_fips year)

* Recode 0 as missing
qui recode lbf emp ump (0=.)

* Generate unemployment rate
qui gen unr = ump/lbf
qui label var unr "Unemployment rate (%)"

* Order, sort, and save
qui order cty_fips year
qui sort cty_fips year
qui save "$projdir/dta/build/cln/laus_cty_NYCnocombine.dta", replace

* Close log file
log close sublog
